﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Data;
using BookManage.Model;

namespace BookManage.DAL
{
    public class BorrowDAL
    {
        //借书后 在TB_Borrow表 生成一条借阅记录
        public static int Add(Borrow borrow)
        {
            int rows = 0;
            string sql = "insert into TB_Borrow(BorrowID,rdID,bkID,IdContinueTimes,IdDateOut,IdDateRetPlan, IdDateRetAct,IdOverDay,IdOverMoney,IdPunishMoney,IsHasReturn,OperatorLend,OperatorRet)"
                 + " values(@BorrowID,@rdID,@bkID,@IdContinueTimes,@IdDateOut,@IdDateRetPlan,@IdDateRetAct,@IdOverDay,@IdOverMoney,@IdPunishMoney,@IsHasReturn,@OperatorLend,@OperatorRet)";
            SqlParameter[] parameters ={
                new SqlParameter("@BorrowID",borrow.BorrowID),
                 new SqlParameter("@rdID",borrow.rdID),
                  new SqlParameter("@bkID",borrow.bkID),
                   new SqlParameter("@IdContinueTimes",borrow.IdContinueTimes),
                    new SqlParameter("@IdDateOut",borrow.IdDateOut),
                     new SqlParameter("@IdDateRetPlan",borrow.IdDateRetPlan),
                      new SqlParameter("@IdDateRetAct",borrow.IdDateRetAct),
                      new SqlParameter("@IdOverDay",borrow.IdOverDay),
                        new SqlParameter("@IdOverMoney",borrow.IdOverMoney),
                   new SqlParameter("@IdPunishMoney",borrow.IdPunishMoney),
                    new SqlParameter("@IsHasReturn",borrow.IsHasReturn),
                     new SqlParameter("@OperatorLend",borrow.OperatorLend),
                      new SqlParameter("@OperatorRet",borrow.OperatorRet)
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);//通过类名访问static方法
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }

        //借书后 TB_Book表 书本数量和状态改变
        public static int UpdateBook(Borrow borrow)
        {
            int rows = 0;
            string sql = "update TB_Book set bkNum=bkNum-1,bkStatus='借出' where bkID=@bkID";
            SqlParameter[] parameters =
            {
                new SqlParameter("@bkID",borrow.bkID)
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }

        public static int UpdatePay(int rdID, int bkID, float IdPunishMoney)  //赔偿书籍，已赔款=书价格，赔偿后：应赔款=0，超期0，已归还
        {
            int rows = 0;
            string sql = "update TB_Borrow set IdPunishMoney=@IdPunishMoney,IdOverMoney=0,IdOverDay=0,IsHasReturn=1 where rdID=@rdID and bkID=@bkID";
            SqlParameter[] parameters =
            {
                new SqlParameter("@rdID",rdID),
                new SqlParameter("@bkID",bkID),
                new SqlParameter("@IdPunishMoney",IdPunishMoney)
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }

        //借书后 TB_Reader表 读者借书本数更新
        public static int UpdateReader(Reader reader)
        {
            int rows = 0;
            string sql = "update TB_Reader set rdBorrowQty=rdBorrowQty+1 where rdID=@rdID";
            SqlParameter[] parameters =
            {
                new SqlParameter("@rdID",reader.rdID)
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }
        
       //还书时更新借阅记录
        public static int UpdateBrwByLend(Borrow borrow)
        {
            int rows = 0;
            string sql = "update TB_Borrow set "
                + "IdContinueTimes=0,"
                + "IdDateRetAct=@IdDateRetAct,"
                + "IdOverDay=@IdOverDay,"
                + "IdOverMoney=@IdOverMoney,"
                + "IsHasReturn=1 "
                + " where BorrowID=@BorrowID";
            SqlParameter[] parameters ={
                                           new SqlParameter("@BorrowID",borrow.BorrowID),
                                           new SqlParameter("@IdDateRetAct",borrow.IdDateRetAct),
                                           new SqlParameter("@IdOverDay",borrow.IdOverDay),
                                           new SqlParameter("@IdOverMoney",borrow.IdOverMoney)
                                      };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }
        

        public static int UpdateBookByPay(Borrow borrow) //赔偿书籍，书籍状态为遗失
        {
            int rows = 0;
            string sql = "update TB_Book set bkStatus='遗失' where bkID=@bkID";
            SqlParameter[] parameters ={
                                           new SqlParameter("@bkID",borrow.bkID)
                                       };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }

        public static DataTable GetLastBrwID()
        {
            string sql = "select top 1 * from TB_Borrow order by BorrowID desc";
            return SqlHelper.GetDataTable(sql, null, "TB_Borrow");
        }  //获取数据库TB_Borrow表最后一列借阅序号，自动生成BorrowID

        //还书时确定图书超期，更新TB_Borrow表 的IdOverDay，IdOverMoney
        public static int UpdateBrwByPunish(int BorrowID, float IdOverDay, float IdOverMoney)
        {
            int rows = 0;
            string sql = "update TB_Borrow set IdOverDay=@IdOverDay,IdOverMoney=@IdOverMoney where BorrowID=@BorrowID";
            SqlParameter[] parameters =
            {
                new SqlParameter("@BorrowID",BorrowID),
                new SqlParameter("IdOverDay",IdOverDay),
                new SqlParameter("@IdOverMoney",IdOverMoney)
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }

        public static int Delete(Borrow borrow)
        {
            int rows = 0;
            string sql = "delete from TB_Borrow where BorrowID=@BorrowID";
            SqlParameter[] parameters ={
                new SqlParameter("@BorrowID",borrow.BorrowID) };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);//通过类名访问static方法
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;

        }
        public static int UpdateBookByLend(Borrow borrow)
        {
            int rows = 0;
            string sql = "update TB_Book set bkNum=bkNum+1,bkStatus='在馆' where bkID=@bkID";
            SqlParameter[] parameters ={
                                           new SqlParameter("@bkID",borrow.bkID)
                                       };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }
        public static int UpdateReaderByLend(Borrow borrow)
        {
            int rows = 0;
            string sql = "update TB_Reader set rdBorrowQty=rdBorrowQty-1 where rdID=@rdID";
            SqlParameter[] parameters ={
                                           new SqlParameter("@rdID",borrow.rdID)
                                       };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }


        public static DataRow GetDRByID(int rdID, int bkID)
        {
            string sql = "select * from TB_Borrow where rdID=@rdID and bkID=@bkID";
            SqlParameter[] parameters =
            {
                new SqlParameter("@rdID", rdID),
                new SqlParameter("@bkID", bkID),
            };
            DataTable dt = null;
            dt = SqlHelper.GetDataTable(sql, parameters, "TB_Borrow");
            DataRow dr = null;
            if (dt == null || dt.Rows.Count == 0)
            {
                return dr;
            }
            else
            {
                dr = dt.Rows[0];
                return dr;
            }
        }
        public static Borrow GetObjectByBrwID(int BrrowID)
        {
            DataRow dr = GetDRByBrwID(BrrowID);
            return SqlHelper.DataRowToT<Borrow>(dr);
        }
        private static DataRow GetDRByBrwID(int borrowID)
        {
            string sql = "select * from TB_Borrow where borrowID=@borrowID";
            SqlParameter[] parameters =
            {
                new SqlParameter("@borrowID", borrowID)
            };
            DataTable dt = null;
            dt = SqlHelper.GetDataTable(sql, parameters, "TB_Borrow");
            DataRow dr = null;
            if (dt == null || dt.Rows.Count == 0)
            {
                return dr;
            }
            else
            {
                dr = dt.Rows[0];
                return dr;
            }
        }

        public static Borrow GetObjectByBkID(int bkID)
        {
            DataRow dr = GetDRByBkID(bkID);
            return SqlHelper.DataRowToT<Borrow>(dr);
        }
        public static DataRow GetDRByBkID(int bkID)
        {
            string sql = "select * from TB_Borrow where bkID=@bkID";
            SqlParameter[] parameters =
            {
                new SqlParameter("@bkID", bkID)
            };
            DataTable dt = null;
            dt = SqlHelper.GetDataTable(sql, parameters, "TB_Borrow");
            DataRow dr = null;
            if (dt == null || dt.Rows.Count == 0)
            {
                return dr;
            }
            else
            {
                dr = dt.Rows[0];
                return dr;
            }
        }

        public static Borrow GetObjectByID(int rdID,int bkID)
        {
            DataRow dr = GetDRByID(rdID,bkID);
            return SqlHelper.DataRowToT<Borrow>(dr);
        }
        
        //续借，更新借书表
        public static int UpdateContinue(Borrow borrow)
        {
            int rows = 0;
            string sql = "update TB_Borrow set "
                + "IdContinueTimes=@IdContinueTimes+1,"
                + "IdDateOut=@IdDateOut,"
                + "IdDateRetPlan=@IdDateRetPlan "
                + "where BorrowID=@BorrowID";
            SqlParameter[] parameters ={
                                           new SqlParameter("@BorrowID",borrow.BorrowID),
                                           new SqlParameter("@IdContinueTimes",borrow.IdContinueTimes),
                                           new SqlParameter("@IdDateOut",borrow.IdDateOut),
                                           new SqlParameter ("@IdDateRetPlan",borrow.IdDateRetPlan)
                                      };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }


        public static DataTable GetReaderType(int rdType)
        {
            string sql = "select CanLendQty,CanLendDay,CanContinueTimes,PunishRate "
                            + "from TB_ReaderType where rdType=@rdType";
            SqlParameter[] parameters =
            {
                 new SqlParameter ("@rdType",rdType)
            };
            return SqlHelper.GetDataTable(sql, parameters, "TB_ReaderType");
        }

        public static DataTable GetrdStatus(int rdID)
        {
            string sql = "select rdStatus,rdBorrowQty from TB_Reader where rdID=@rdID";
            SqlParameter[] parameters ={
                                          new SqlParameter ("@rdID",rdID)
                                      };
            return SqlHelper.GetDataTable(sql, parameters, "TB_Reader");
        }

        public static DataTable GetBookName(string bkName)
        {
            bkName = (bkName == "") ? ("%") : ("%" + bkName + "%");
            string sql = "select * from TB_Book where bkName like @bkName";
            SqlParameter[] parameters ={
                                           new SqlParameter("@bkName",bkName)
                                      };
            return SqlHelper.GetDataTable(sql, parameters, "TB_Book");

        }

        public static DataTable GetBook(int bkID, string bkName)
        {
            string sql;
            if (bkID <= 0 && bkName == "")
            {
                sql = "select * from TB_Book where 0=1";
                return SqlHelper.GetDataTable(sql, null, "TB_Book");
            }
            bkName = (bkName == "") ? ("%") : ("%" + bkName + "%");
            if (bkID <= 0)
            {
                sql = "select * from TB_Book where bkName like @bkName";
                SqlParameter[] parameters =
                {
                    new SqlParameter("@bkName",bkName)
                };
                return SqlHelper.GetDataTable(sql, parameters, "TB_Book");
            }
            else
            {
                sql = "select * from TB_Book where bkID=@bkID and bkName like @bkName";
                SqlParameter[] parameters =
                {
                    new SqlParameter("@bkID",bkID),
                    new SqlParameter("@bkName",bkName)
                };
                return SqlHelper.GetDataTable(sql, parameters, "TB_Book");
            }
        }

        public static DataTable GetBorrow(int rdID)
        {
            string sql;
            if (rdID == 0)
            {
                return null;
            }
            else
            {
                 /*sql = "select TB_Borrow.BorrowID,TB_Borrow.rdID,TB_Book.bkID,TB_Book.bkName,TB_Book.bkAuthor,TB_Borrow.IdContinueTimes,TB_Borrow.IdDateOut,TB_Borrow.IdDateRetPlan,TB_Borrow.IdOverDay,TB_Borrow.IdOverMoney "
                         + "from TB_Book,TB_Reader,TB_Borrow  where TB_Book.bkID=TB_Borrow.bkID and TB_Borrow.rdID=@rdID and TB_Borrow.IsHasReturn=0";
                 SqlParameter[] parameters ={
                                               new SqlParameter ("@rdID",rdID)
                                           };
                 return SqlHelper.GetDataTable(sql, parameters, "TB_Book");*/
                sql = "select * from TB_Borrow where rdID=@rdID";
                SqlParameter[] parameters ={
                                               new SqlParameter ("@rdID",rdID)
                                           };
                return SqlHelper.GetDataTable(sql, parameters, "TB_Borrow");
            }
        }
        public static DataTable GetReader(int rdID)
        {
            if (rdID == 0)
            {
                return null;
            }
            else
            {
                string sql = "select * from TB_Reader where rdID=@rdID";
                SqlParameter[] parameters = { new SqlParameter("@rdID", rdID) };
                return SqlHelper.GetDataTable(sql, parameters, "TB_Reader");
            }
        }

        public static int Update(Borrow borrow)
        {
            int rows = 0;
            string sql = "update TB_Borrow set "
                //+ "BorrowID=@BorrowID,"
                + "rdID= @rdID,"
                + "bkID=@bkID,"
                + "IdContinueTimes=@IdContinueTimes,"
                + "IdDateOut=@IdDateOut,"
                + "IdDateRetPlan=@IdDateRetPlan,"
                + "IdDateRetAct=@IdDateRetAct,"
                + "IdOverDay=@IdOverDay,"
                + "IdOverMoney=@IdOverMoney,"
               + "IdPunishMoney=@IdPunishMoney,"
               + "IsHasReturn=@IsHasReturn,"
               + "OperatorLend=@OperatorLend,"
               + "OperatorRet=@OperatorRet "
               + "where BorrowId=@BorrowID";
            SqlParameter[] parameters ={
               new SqlParameter("@BorrowID",borrow.BorrowID),
                 new SqlParameter("@rdID",borrow.rdID),
                  new SqlParameter("@bkID",borrow.bkID),
                   new SqlParameter("@IdContinueTimes",borrow.IdContinueTimes),
                    new SqlParameter("@IdDateOut",borrow.IdDateOut),
                     new SqlParameter("@IdDateRetPlan",borrow.IdDateRetPlan),
                      new SqlParameter("@IdDateRetAct",borrow.IdDateRetAct),
                      new SqlParameter("@IdOverDay",borrow.IdOverDay),
                        new SqlParameter("@IdOverMoney",borrow.IdOverMoney),
                   new SqlParameter("@IdPunishMoney",borrow.IdPunishMoney),
                    new SqlParameter("@IsHasReturn",borrow.IsHasReturn),
                     new SqlParameter("@OperatorLend",borrow.OperatorLend),
                      new SqlParameter("@OperatorRet",borrow.OperatorRet)
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);//通过类名访问static方法
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }
        //缴纳超期金额后，更新TB_Borrow表
        public static int UpdatePunish(int rdID, int bkID,float IdPunishMoney,DateTime IdDateRetPlan)
        {
            int rows = 0;
            string sql = "update TB_Borrow set IdPunishMoney=@IdPunishMoney,IdOverMoney=IdOverMoney-@IdPunishMoney,IdOverDay=0,IdDateRetPlan=@IdDateRetPlan where rdID=@rdID and bkID=@bkID";
            SqlParameter[] parameters =
            {
                new SqlParameter("@rdID",rdID),
                new SqlParameter("@bkID",bkID),
                new SqlParameter("@IdPunishMoney",IdPunishMoney),
                new SqlParameter("@IdDateRetPlan",IdDateRetPlan)
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        }

    }
}